﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using System.Text;
using Module.Common.Models;
using Module.Core.EntitiesInfo.TablesInfo;

namespace Module.Core.DAO
{
   public class CityDAO

    {
       #region Fields

		protected string connectionStringName;

		#endregion
	
		#region Methods

        /// <summary>
        /// Saves a record to the City table.
        /// </summary>
        public virtual int Insert(CityInfo cityInfo)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@CityName", cityInfo.CityName),
				new SqlParameter("@IsActive", cityInfo.IsActive),
				new SqlParameter("@CreatedBy", cityInfo.CreatedBy),
				new SqlParameter("@Priority", cityInfo.Priority),
				new SqlParameter("@CreatedOn", cityInfo.CreatedOn)
			};

            return cityInfo.CityId = (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "City_Insert", parameters);
        }


		/// <summary>
		/// Updates a record in the City table.
		/// </summary>
		public virtual void Update(CityInfo cityInfo)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@CityId", cityInfo.CityId),
				new SqlParameter("@CityName", cityInfo.CityName),
				new SqlParameter("@IsActive", cityInfo.IsActive),
				new SqlParameter("@CreatedBy", cityInfo.CreatedBy),
				new SqlParameter("@Priority", cityInfo.Priority),
				new SqlParameter("@CreatedOn", cityInfo.CreatedOn)
			};

			SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "City_Update", parameters);
		}

		/// <summary>
		/// Deletes a record from the City table by its primary key.
		/// </summary>
		public virtual void Delete(int cityId)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@CityId", cityId)
			};

            SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "City_Delete", parameters);
		}

		/// <summary>
		/// Selects a single record from the City table.
		/// </summary>
		public virtual CityInfo Select(int cityId)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@CityId", cityId)
			};

			using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "City_Select", parameters))
			{
				if (dataReader.Read())
				{
					return MakeCityInfo(dataReader);
				}
				else
				{
					return null;
				}
			}
		}
      
		/// <summary>
		/// Selects all records from the City table.
		/// </summary>
		public CHRTList<CityInfo> SelectAll()
		{
			using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "City_SelectAll"))
			{
				CHRTList<CityInfo> cityInfoList = new CHRTList<CityInfo>();
				while (dataReader.Read())
				{
					CityInfo cityInfo = MakeCityInfo(dataReader);
					cityInfoList.Add(cityInfo);
				}

				return cityInfoList;
			}
		}


        public virtual int SelectCount(string cityName, bool isTotalRow)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{           
                  new SqlParameter("@CityName", cityName),
				new SqlParameter("@isTotalRow", true.ToString())
			};

            return (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "City_Search", parameters);
        }
        public virtual DataTable Search(string cityName,int beginRow, int endRow, string orderByColumn, bool isTotalRow)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{      
                new SqlParameter("@CityName", cityName),
                new SqlParameter("@BeginRow", beginRow),
                new SqlParameter("@EndRow", endRow),
                new SqlParameter("@OrderByColumn", orderByColumn),
                new SqlParameter("@IsTotalRow", false.ToString())
			};

            return SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "City_Search", parameters);
        }



		/// <summary>
		/// Creates a new instance of the City class and populates it with data from the specified SqlDataReader.
		/// </summary>
		protected virtual CityInfo MakeCityInfo(SqlDataReader dataReader)
		{
			CityInfo cityInfo = new CityInfo();
			cityInfo.CityId = SqlClientUtility.GetInt32(dataReader,DbConstants.CITY.CITYID, 0);
			cityInfo.CityName = SqlClientUtility.GetString(dataReader,DbConstants.CITY.CITYNAME, String.Empty);
			cityInfo.IsActive = SqlClientUtility.GetBoolean(dataReader,DbConstants.CITY.ISACTIVE, false);
			cityInfo.CreatedBy = SqlClientUtility.GetString(dataReader,DbConstants.CITY.CREATEDBY, String.Empty);
			cityInfo.Priority = SqlClientUtility.GetInt32(dataReader,DbConstants.CITY.PRIORITY, 0);
			cityInfo.CreatedOn = SqlClientUtility.GetDateTime(dataReader,DbConstants.CITY.CREATEDON, DateTime.Now);

			return cityInfo;
		}

		#endregion
    }
}
